﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SMS_Server
{
    class Action_Search:Incoming_Action
    {

        public string ActionName;
        public string ActionFormat;
        public string[] ParamNames = null;
        public Connectors Connector;
        public string ConnectionString;
        public string TableName;
        public string SearchColumns;//Comma seperated column names
        public string ResponseColumns;//Comma seperated list of response column names
        public string ResponseFormat;
        //Use reserved word, [PhoneNumber] for the phone number and [Date] for date time parameter


        public enum Connectors
        {
            SQL, OleDb, Odbc
        }

        public bool GetResult(string PhoneNumber, object[] Params, out string result)
        {
            if (ParamNames == null)
            {
                ActionFormat = ActionFormat.Replace("[", "").Replace("]", "");
                ParamNames = ActionFormat.Split(' ');
            }
            if (Params.Length != ParamNames.Length)
            {
                string resp = "Please write in the given format " + ActionName.ToUpper();
                for (int i = 0; i < ParamNames.Length; i++)
                {
                    resp += " ";
                    resp += "[" + ParamNames[i] + "]";
                }
                result = resp; return true;
            }
            try
            {
                iDataAccess dta;
                if (Connector == Connectors.Odbc)
                {
                    dta = new OdbcDataAccess(ConnectionString);
                }
                else if (Connector == Connectors.OleDb)
                {
                    dta = new OleDataAccess(ConnectionString);
                }
                else
                {
                    dta = new SQLDataAccess(ConnectionString);
                }
                string[] cols = SearchColumns.Split(',');

                string[] resp_cols = ResponseColumns.Split(',');
                string command = "SELECT ";
                for (int i = 0; i < resp_cols.Length; i++)
                {
                    command += resp_cols[i] + ",";
                }
                if (command[command.Length - 1] == ',') command = command.Substring(0, command.Length - 1);
                command += " FROM " + TableName + " WHERE ";
                for (int i = 0; i < cols.Length; i++)
                {
                    if (cols[i].Split('=')[1] == "[Date]")
                    {
                        command += cols[i].Split('=')[0] + " = @" + cols[i].Split('=')[0] + " AND ";
                    }
                    else
                        command += cols[i].Split('=')[0] + " LIKE @" + cols[i].Split('=')[0] + " AND ";
                }
                if (command.Substring(command.Length - 5) == " AND ") command = command.Substring(0, command.Length - 5);
                command += ";";
                dta.CommandString = command;
                for (int i = 0; i < cols.Length; i++)
                {
                    if (cols[i].Split('=')[1] == "[PhoneNumber]")
                    {
                        dta.AddParameter("@" + cols[i].Split('=')[0], "%" + PhoneNumber + "%");
                        break;
                    }
                    if (cols[i].Split('=')[1] == "[Date]")
                    {
                        if (Connector == Connectors.OleDb)
                            dta.AddParameter("@" + cols[i].Split('=')[0], DateTime.Now.ToOADate());
                        else
                            dta.AddParameter("@" + cols[i].Split('=')[0], DateTime.Now);
                        break;
                    }
                    bool hasFound = false;
                    for (int x = 0; x < ParamNames.Length; x++)
                    {
                        if (cols[i].Split('=')[1] == "[" + ParamNames[x] + "]")
                        {
                            dta.AddParameter("@" + cols[i].Split('=')[0], "%" + Params[x] + "%");
                            hasFound = true;
                            break;
                        }
                    }
                    if (!hasFound)
                    {
                        System.Diagnostics.EventLog el = new System.Diagnostics.EventLog();
                        el.Log = "Application";
                        el.MachineName = ".";
                        el.Source = "SMS Server";
                        el.WriteEntry("Error in Search: Parameter name has not been defined. Please re-check using SMS server manager.", System.Diagnostics.EventLogEntryType.Error);

                        result = "";
                        //DataAccess DA = new DataAccess(Global.GetUsageDatabaseName(), Global.DatabasePassword);
                        //DA.CommandString = "INSERT INTO ErrorList(ClassName, Args, Error, ErrorDate) VALUES (@ClassName, @Args, @Error, @ErrorDate)";
                        //DA.AddParameter("@ClassName", "Search");
                        //DA.AddParameter("@Args", "");
                        //DA.AddParameter("@Error", "Parameter name has not been defined");
                        //DA.AddParameter("@ErrorDate", DateTime.Now);
                        //DA.ExecuteNonQuery();
                        //DA.Close();
                        return false;
                    }
                }
                dta.ExecuteReader();

                result = "";
                string message = ResponseFormat;
                bool foundResponse = false;
                int count = 1;

                while (dta.Reader.Read())
                {
                    if (count > 5) break;
                    message = ResponseFormat;
                    for (int i = 0; i < resp_cols.Length; i++)
                    {
                        if (dta.Reader.IsDBNull(i)) { message = message.Replace("[" + resp_cols[i] + "]", ""); continue; }
                        message = message.Replace("[" + resp_cols[i] + "]", dta.Reader.GetString(i));
                    }
                    foundResponse = true;
                    if (result != "") result += ". ";
                    result += count + ". " + message;
                    count++;
                }
                dta.Reader.Close();
                dta.Close();
                if (!foundResponse)
                {
                    result = "No result found";
                    return true;
                }

            }
            catch (Exception ex)
            {
                System.Diagnostics.EventLog el = new System.Diagnostics.EventLog();
                el.Log = "Application";
                el.MachineName = ".";
                el.Source = "SMS Server";
                el.WriteEntry("Error in Forward2TCP: " + ex.Message, System.Diagnostics.EventLogEntryType.Error);

                string comm = ActionName.ToUpper();
                for (int i = 0; i < Params.Length; i++)
                {
                    comm += " ";
                    comm += "[" + Params[i] + "]";
                }
                result = "";
                //DataAccess DA = new DataAccess(Global.GetUsageDatabaseName(), Global.DatabasePassword);
                //DA.CommandString = "INSERT INTO ErrorList(ClassName, Args, Error, ErrorDate) VALUES (@ClassName, @Args, @Error, @ErrorDate)";
                //DA.AddParameter("@ClassName", "Search");
                //DA.AddParameter("@Args", comm);
                //DA.AddParameter("@Error", ex.Message);
                //DA.AddParameter("@ErrorDate", DateTime.Now);
                //DA.ExecuteNonQuery();
                //DA.Close();
                return false;
            }
            return true;
        }
    }
}
